package util;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.lychie.jexcel.ReadableExcel;
import org.lychie.jexcel.demo.model.Person;
import org.lychie.jexcel.exception.ValidationCastException;
import org.lychie.jexcel.validity.BasicValidation;
import org.lychie.jutil.IOUtil;
import org.lychie.jutil.Printer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ReadContentUtil {

	 public static  Logger LOGGER = LoggerFactory.getLogger(WriteContentUtil.class);
	 
	 public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	 
		/**
		 * @param PropertyHead  头部属性
		 * 
		 * @param initData     初始化数据
		 * 
		 * @param writePath    写入文件路径        
		 * 
		 * @param tableName    表名
		 */
		public static void readFromFile(int SheetIndex,List<Map<String,Object>> PropertyHead,String originalPath,String destinaPath,String tableName){
			// 创建一个可读的EXCEL对象
			ReadableExcel excel = new ReadableExcel(Person.class);
			excel.setSheetIndex(SheetIndex);//读取sheet Index 
			// 设置POJO属性与EXCEL单元格的映射关系
			String key="";
			StringBuffer insertColumn =new StringBuffer();
			String insertColumns="";
			String generatorSql="";
			// 设置POJO属性与EXCEL单元格的映射关系
			if(null!=PropertyHead&&PropertyHead.size()>0){
				for(Map<String,Object> entry:PropertyHead){
					Iterator<Entry<String, Object>> it = entry.entrySet().iterator();
					  while (it.hasNext()) {
						   Map.Entry<String, Object> map = it.next();
						    key=map.getKey()==null?"":map.getKey().toString();
						   String value=map.getValue()==null?"":map.getValue().toString();
						   LOGGER.info("key===>"+key+",====value>>"+value);
						   excel.setMapper(key,value);
						   
						   insertColumn.append(key+",");
					  }
				}
				  insertColumns=insertColumn.substring(0, insertColumn.length()-1);  //sql插入数据的列
				 LOGGER.info("====insertColumns====>"+insertColumns);
			}
			// 载入EXCEL文档
			excel.load(IOUtil.getResourceAsStream("persons.xlsx"));
			
			try {
				// 校验EXCEL文档内容的合法性
				excel.validate(new MyValidation());
			} catch (ValidationCastException e) {
				e.printStackTrace();
				return ;
			}
			
			// 解析EXCEL文档成集合
			List<Person> list = excel.toList();
			// 打印输出集合的内容
			Printer.print(list);
			LOGGER.info("======数据总记录条数:=====>"+list.size());
			
			
			//并写入文件中：
			WriteContentUtil.writeToFile(PropertyHead, list, destinaPath);
			
			for(Person per:list){
				//id,sex,name,age,date
				generatorSql="insert into "+tableName+" ( "+insertColumns +")" +" values ("+per.getId()+","+per.getSex()+","
				+per.getName()+","+per.getAge()+","+per.getDate()+" ) ;";
				
				System.out.println();
				
				System.out.println("generatorSql===>"+generatorSql);
			}
			
			//将sql 脚本写入*.sql文件中
			
			WriteContentUtil.writeToFile(PropertyHead, list, "src/main/resources/person.txt");  //写入乱码？？？？？
			
		}
		
		private static class MyValidation extends BasicValidation {
			@Override
			public boolean validate(Class<?> type, String name, String value) {
				// 自定义校验规则, 在 super.validate 之前校验, 以达到短路父类校验规则的目的
				if (name.equals("age")) {
					Integer age = Integer.valueOf(value);
					if (age >= 18 && age < 55) {
						return true;
					} else {
						setCause("年龄不在 [18, 55) 区间");
						return false;
					}
				}
				// 最后调父类校验方法
				return super.validate(type, name, value);
			}

		}
		
		//测试
		public static void main(String[] args) {
			
			List<Map<String,Object>>  ProHeadList=new ArrayList<Map<String,Object>>();
			Map<String,Object> headmap=new HashMap<String, Object>();
			headmap.put("id", "编号");
			headmap.put("age", "年龄");
			headmap.put("sex", "性别");
			headmap.put("name", "姓名");
			headmap.put("date", "生日");
			ProHeadList.add(headmap);
			
			System.out.println("begin Date====>"+sdf.format(new Date()));
			
			readFromFile(0,ProHeadList,"persons.xlsx","src/main/resources/writePerson.xlsx","Person");
			
			System.out.println("end Date====>"+sdf.format(new Date()));
		}
}
